#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 导入相应的库文件
import urllib.request
import os
import mysql.connector

# base_dir="E:\data\小说\\"
statistic_sql='''
select a.type_id as id,a.type_name as 类型,a.number_count as 总数, b.number_count as 已下载数 from 
(select count(1) as number_count ,type_name,type_id from fiction_table a group by a.type_name,type_id) a 
left join 
(select count(1)  as number_count ,type_name,type_id from fiction_table a where a.download=1 group by a.type_name,type_id) b 
on a.type_id=b.type_id
order by a.type_id asc
'''
get_file_sql = 'select * from fiction_table where download is null and type_id in (17,13,14) order by rand() limit '
success_update_sql = 'update fiction_table set download=1 where id = '
error_update_sql = 'update fiction_table set download=-1 where id = '
mydb = mysql.connector.connect(
    host="192.168.31.11",  # 数据库主机地址
    port='3316',
    user="root",  # 数据库用户名
    database="test",  # 数据库名称 没有的话插入操作报错
    passwd="211314wccA"  # 数据库密码

)
mycursor = mydb.cursor()
mycursor.execute(statistic_sql)
results = mycursor.fetchall()

def print_table(results):
    if not results:
        print("No results found.")
        return
    # 获取列名
    column_names = [desc[0] for desc in mycursor.description]
    print(" | ".join(column_names))
    print("-" * 40)

    for row in results:
        print(" | ".join(str(item) for item in row))

print_table(results)
def down_file(i):
    mycursor.execute(get_file_sql+i.__str__())
    results = mycursor.fetchall()
    index_num=1
    for row in results:
        id = row[0]
        f_name = row[1]
        f_code = row[2]
        type_id = row[3]
        type_name = row[4]
        download = row[5]
        download_file_to_dir(id,f_name,type_name,f_code,index_num)
        index_num+=1


def download_file_to_dir(id,file_name,type,f_code,index_num):
    url = "https://down7.ixdzs8.com/" + str(f_code) + ".zip"
    #print()
    try:
        if not os.path.exists('/home/wcc/下载/fiction/' + type + '/'):
            os.makedirs('/home/wcc/下载/fiction/' + type + '/')
        print(index_num.__str__()+"==下载url: "+url+'  位置：/home/wcc/下载/fiction/' + type + '/' + file_name + ".zip")
        urllib.request.urlretrieve(url, '/home/wcc/下载/fiction/' + type + '/' + file_name + ".zip")
        mycursor.execute(success_update_sql+id.__str__())
        mydb.commit()
    except Exception as e:
        print("===捕获异常" + e.__str__())
        mycursor.execute(error_update_sql+id.__str__())
        mydb.commit()

# 程序主入口
if __name__ == '__main__':
    down_file(100)
